//存在问题，plant simulation表格中，站位编号和人员编号不具有唯一性，无法作为数据库表格的主键，可能需要一行存多组数据，暂时无法正常运行
//添加自动递增序列作为主键，使用站位号编号和人员编号作为索引
//导出，运行，导入，后续拆开为三个响应，防止并行      执行顺序问题
//字段设置为order会与sql关键字冲突报错
//注意ps excel sql三者间数据类型

var models = require('../db');
var express = require('express');
var router = express.Router();
var mysql = require('mysql');

const { exec } = require('child_process');  //执行外部命令或者脚本
//exec为异步执行

//导出excel
const fs = require('fs');
const xlsx = require('xlsx');
// 连接数据库
var conn = mysql.createConnection(models.mysql);
conn.connect();


//增删改查
router.get('/query',(req,res)=>{
    conn.query('select * from ao_list',function(err,row){
        if(err){
             console.log(err)            
        }
        let data = JSON.stringify(row)
        res.end(data)
    })
});

//url:http://localhost:8000/api/plant_in/export
//先从数据导出excel数据，执行control.py将excel数据导入plantsimulation，仿真结束后，导出数据为excel并存储至数据库响应表格
router.get('/export', (req, res) => {
    const querySQL1 = `
        SELECT name, number, code, work_time, parallel_work_time, remaining_work_time, T_class,
        T_number, important, batch, execute, kitting_time, position_zw, auto, instrument
        FROM ao_list
    `;
    const querySQL2 = `
        SELECT group_number, workername_1, skill_level, position_zw
        FROM input_groupinformation
    `;
    const querySQL3 = `
        SELECT name, exception_duration
        FROM input_equipmentinformation
    `;
    const querySQL4 = `
        SELECT number, name, batch, delivery_time, available, available_max, buffer_in, buffer_out
        FROM batch
    `;

    // 用于存储所有查询结果
    const allResults = {};

    // 执行所有查询
    conn.query(querySQL1, (err, results1) => {
        if (err) {
            console.log(err);
            return res.status(500).send('Database query error');
        }
        allResults.sheet1 = results1;

        conn.query(querySQL2, (err, results2) => {
            if (err) {
                console.log(err);
                return res.status(500).send('Database query error');
            }
            allResults.sheet2 = results2;

            conn.query(querySQL3, (err, results3) => {
                if (err) {
                    console.log(err);
                    return res.status(500).send('Database query error');
                }
                allResults.sheet3 = results3;

                conn.query(querySQL4, (err, results4) => {
                    if (err) {
                        console.log(err);
                        return res.status(500).send('Database query error');
                    }
                    allResults.sheet4 = results4;

                    // 所有查询完成后，生成 Excel 文件
                    const workbook = xlsx.utils.book_new();

                    // 添加自定义表头到工作表 1 (AOList)
                    const header1 = [["AO名称", "AO编号", "AO代号", "工时", "并行工时", "剩余工时", "T级", "T级数量", "AO关键重要属性", "批次", "AO是否可执行", "齐套时间", "所属站位", "是否自动设备执行", "工装型号"]];
                    const worksheet1 = xlsx.utils.aoa_to_sheet(header1);
                    xlsx.utils.sheet_add_json(worksheet1, allResults.sheet1, { origin: "A2", skipHeader: true });
                    
                    // 添加自定义表头到工作表 2 (Worker)
                    const header2 = [["人员组编号", "人员组名称", "技能等级", "所在岗位"]];
                    const worksheet2 = xlsx.utils.aoa_to_sheet(header2);
                    xlsx.utils.sheet_add_json(worksheet2, allResults.sheet2, { origin: "A2", skipHeader: true });

                    // 添加自定义表头到工作表 3 (Equipment)
                    const header3 = [["设备名称", "故障持续时间"]];
                    const worksheet3 = xlsx.utils.aoa_to_sheet(header3);
                    xlsx.utils.sheet_add_json(worksheet3, allResults.sheet3, { origin: "A2", skipHeader: true });

                    // 添加自定义表头到工作表 4 (Batch)
                    const header4 = [["站位编号", "站位名称", "站位批次设置", "站位批次交付时间", "可用工位数", "最大可用工位数", "入缓存容量", "出缓存区容量"]];
                    const worksheet4 = xlsx.utils.aoa_to_sheet(header4);
                    xlsx.utils.sheet_add_json(worksheet4, allResults.sheet4, { origin: "A2", skipHeader: true });

                    // 将工作表添加到 Excel 文件中
                    xlsx.utils.book_append_sheet(workbook, worksheet1, 'AOList');
                    xlsx.utils.book_append_sheet(workbook, worksheet2, 'Worker');
                    xlsx.utils.book_append_sheet(workbook, worksheet3, 'Equipment');
                    xlsx.utils.book_append_sheet(workbook, worksheet4, 'Batch');

                    // 保存 Excel 文件
                    const excelFilePath = './plant_data.xlsx';
                    if (fs.existsSync(excelFilePath)) {
                        fs.unlinkSync(excelFilePath);
                    }
                    xlsx.writeFile(workbook, excelFilePath);

                    // 发送成功响应
                    res.status(200).send('File generated successfully');
                    
                    console.log('File generated and saved: ' + excelFilePath);

                 
                });
            });
        });
    });



});

//-----------------------------------------------------------------------------------------------------------------------------------------------------
//---------------------------------------------control-------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------------------------
//url:http://localhost:8000/api/plant_in/run


router.post('/run', (req, res) => {
    exec('C:/Users/ZIHAO/anaconda3/python.exe D:/cfproject/cf3/src/server/control.py', (error, stdout, stderr) => {
        if (error) {
            console.error(`Error: ${error.message}`);
            return res.status(500).send(`Error: ${error.message}`);
        }

        if (stderr) {
            console.error(`stderr: ${stderr}`);
            return res.status(500).send(`stderr: ${stderr}`);
        }

        console.log("simulation completed");
        return res.status(200).send('simulation completed');
    });
});


//-----------------------------------------------------------------------------------------------------------------------------------------------------
//---------------------------------------------import--------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------------------------
//url:http://localhost:8000/api/plant_in/import


//plant simulation仿真结束后导出的excel导入数据库
router.post('/import', (req, res) => {
    

    const inputfilePath1 = './gante_pdata.xlsx';  // 确保文件路径正确
    const inputfilePath2 = './gante_people.xlsx';  // 确保文件路径正确

     if (!fs.existsSync(inputfilePath1)) {
        return res.status(404).send('File not found');
    }

    if (!fs.existsSync(inputfilePath2)) {
        return res.status(404).send('File not found');
    }
  

    // 读取 Excel 文件
    const workbook1 = xlsx.readFile(inputfilePath1);
    const sheetName1 = workbook1.SheetNames[0];  // 读取第一个工作表
    const sheet1 = workbook1.Sheets[sheetName1];

    // 将工作表转换为 JSON 格式
    const jsonData1 = xlsx.utils.sheet_to_json(sheet1);

    // 遍历每一行数据，构造 SQL 插入语句
    jsonData1.forEach(row => {
        const position_zw = row['站位名称'];
        const start_time = row['开始装配时间'];
        const end_time = row['结束装配时间'];
        const batch = row['批次'];

        // 构建 SQL 插入语句
        const insertSQL1 = `
            INSERT INTO gante_pdata (position_zw, start_time, end_time, batch)
            VALUES (?, ?, ?, ?)
        `;

        // 执行插入操作
        conn.query(insertSQL1, [position_zw, start_time, end_time, batch], (err) => {
            if (err) {
                console.error('Database insertion error:', err);
                return res.status(500).send('Database insertion error');
            }
        });
    });

    

    const workbook2 = xlsx.readFile(inputfilePath2);
    const sheetName2 = workbook2.SheetNames[0];  // 读取第一个工作表
    const sheet2 = workbook2.Sheets[sheetName2];
    const jsonData2 = xlsx.utils.sheet_to_json(sheet2);

    jsonData2.forEach(row => {
        const code = row['人员编号'];
        const start_time = row['开始作业时间'];
        const duration = row['作业时长'];
        const AO = row['作业指令'];

        // 构建 SQL 插入语句
        const insertSQL2 = `
            INSERT INTO gante_people (code, start_time, duration, AO)
            VALUES (?, ?, ?, ?)
        `;

        // 执行插入操作
        conn.query(insertSQL2, [code, start_time, duration, AO], (err) => {
            if (err) {
                console.error('Database insertion error:', err);
                return res.status(500).send('Database insertion error');
            }
        });
    });
    //避免重复发送成功响应
    res.status(200).send('Data successfully imported into gante_people/gante_pdata');
    console.log("Data successfully imported into gante_people/gante_pdata");
    
});


module.exports = router;//此语句不可缺少
